package cn.hpclub.server.model;

import java.io.Serializable;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.plugin.activerecord.Model;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

@TableBind(tableName = "report_daily_page_view")
public class ReportDailyPageView extends Model<ReportDailyPageView> implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = -7224291989104740211L;
	
	public static final ReportDailyPageView dao = new ReportDailyPageView();
	
	public String getSelect(){
        String select = " select * ";

        return select;
    }

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from report_daily_page_view as p where 1=1 ";
        
        sqlExceptSelect += " and p.org_id in("
                + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), Constant.MAX_ORG_LEVEL, true) + ") ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        
        sqlExceptSelect += " order by login_time desc, telephone asc, login_count desc ";

        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("telephone")){
            sql += " and p.telephone like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("empCode")){
            sql += " and p.emp_code = '" + item.getValue() + "'";
        } else if(item.getKey().equals("module_name")){
            sql += " and p.module_name = '" + item.getValue() + "'";
        } else if(item.getKey().equals("startTime")){
            sql += " and p.login_time >= '" + item.getValue() + "' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and p.login_time <= '" + item.getValue() + "' ";
        } else if(item.getKey().equals("user_id")){
            sql += " and p.user_id = '" + item.getValue() + "' ";
        } else if(item.getKey().equals("city")){
            sql += " and p.city = '" + item.getValue() + "' ";
        } 

        return sql;
    }
    
    public String getSelectEx(ConcurrentHashMap<String, String> searchInfo){
    	String dates = getQueryDates(searchInfo);
    	
        String select = " select sum(pv.login_count) as user_login_count, "+ dates +", pv.* ";

        return select;
    }

    public String getSqlExceptSelectEx(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from report_daily_page_view as pv where 1=1 ";
        
        sqlExceptSelect += " and pv.org_id in("
                + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), Constant.MAX_ORG_LEVEL, true) + ") ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryEx(item);
        }
        
        //String groupBy = " group by pv.org_id ";
        String groupBy = " group by pv.city ";
        String type = getQueryType(searchInfo);
        if("1".equals(type)){
        	groupBy = " group by pv.user_id ";
        }
        
        sqlExceptSelect += groupBy;
        sqlExceptSelect += " order by user_login_count desc ";

        return sqlExceptSelect;
    }

    private String buildQueryEx(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("telephone")){
            sql += " and pv.telephone like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("empCode")){
            sql += " and pv.emp_code = '" + item.getValue() + "'";
        } else if(item.getKey().equals("startTime")){
            sql += " and pv.login_time >= '" + item.getValue() + "' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and pv.login_time <= '" + item.getValue() + "' ";
        } 

        return sql;
    }
    
    private String getQueryDates(ConcurrentHashMap<String, String> searchInfo){
		String startDate = "未知时间";
		String endDate = "至今";
		for(Entry<String, String> item : searchInfo.entrySet()){
			if(item.getKey().equals("startTime")){
				startDate = item.getValue().toString();
	        }else if(item.getKey().equals("endTime")){
	        	endDate = item.getValue().toString();
	        }
        }
		
		return "'" + startDate + "' as startTime, '" + endDate +"'  as endTime ";
    }
    
	private String getQueryType(ConcurrentHashMap<String, String> searchInfo){
		String type = "1";
		for(Entry<String, String> item : searchInfo.entrySet()){
			if(item.getKey().equals("type")){
				type = item.getValue().toString();
	        }
        }
		
		return type;
    }
}
